﻿using NewBeeTools.Core;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NewBeeTools.Files
{
    public class ExcelUtils
    {
        public static string ExcelContentType
        {
            get
            {
                return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            }
        }
        public static ExcelPackage ExportSheet<T>(ExcelPackage package, List<T> data, Dictionary<string, string> headDict, string sheetName = "", bool showSrNo = false)
        {
            return ExportSheet(package, data.ToDataTable<T>(), headDict, sheetName, showSrNo);
        }

        public static ExcelPackage ExportSheet(ExcelPackage package, DataTable dt, Dictionary<string, string> headDict, string sheetName = "", bool showSrNo = false)
        {
            List<string> keyList = new List<string>();
            if (showSrNo)
            {
                keyList.Add("RowNum");
                dt.Columns.Add("RowNum");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i]["RowNum"] = i + 1;
                }
            }
            //通过键的集合取
            foreach (string key in headDict.Keys)
            {
                keyList.Add(key);
            }
            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
            ExcelWorksheet sheet = package.Workbook.Worksheets.Add(sheetName.IsNullOrEmpty() ? "Sheet1" : sheetName);
            if (showSrNo)
            {
                headDict.Add("RowNum", "序号");
            }
            for (int i = 0; i < keyList.Count; i++)
            {
                sheet.Cells[1, i + 1].Value = headDict[keyList[i]];
            }
            if (dt.Rows.Count > 0)
            {

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < keyList.Count; j++)
                    {

                        sheet.Cells[i + 2, j + 1].Value = dt.Rows[i][keyList[j]].ToString();
                    }
                }
            }
            ExcelRange cells = sheet.Cells[1, 1, 1 + dt.Rows.Count, keyList.Count];
            cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
            cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
            cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
            cells.AutoFitColumns();//自适应列宽
            return package;
        }

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="headDict"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static byte[] ExportExcel<T>(List<T> data, Dictionary<string, string> headDict, string sheetName = "", bool showSrNo = false)
        {
            return ExportExcel(data.ToDataTable<T>(), headDict, sheetName, showSrNo);
        }

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dataTable">数据源</param>
        /// <param name="heading">工作簿Worksheet</param>
        /// <param name="showSrNo">//是否显示行编号</param>
        /// <param name="columnsToTake">要导出的列</param>
        /// <returns></returns>
        public static byte[] ExportExcel(DataTable dt, Dictionary<string, string> headDict, string sheetName = "", bool showSrNo = false)
        {
            byte[] result = null;
            List<string> keyList = new List<string>();
            if (showSrNo)
            {
                keyList.Add("RowNum");
                dt.Columns.Add("RowNum");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i]["RowNum"] = i + 1;
                }

            }
            //通过键的集合取
            foreach (string key in headDict.Keys)
            {
                keyList.Add(key);
            }
            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
            using (ExcelPackage package = new ExcelPackage())
            {
                ExcelWorksheet sheet = package.Workbook.Worksheets.Add(sheetName.IsNullOrEmpty() ? "Sheet1" : sheetName);
                if (showSrNo)
                {
                    headDict.Add("RowNum", "序号");
                }
                for (int i = 0; i < keyList.Count; i++)
                {
                    sheet.Cells[1, i + 1].Value = headDict[keyList[i]];
                }
                if (dt.Rows.Count > 0)
                {

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        for (int j = 0; j < keyList.Count; j++)
                        {

                            sheet.Cells[i + 2, j + 1].Value = dt.Rows[i][keyList[j]].ToString();
                        }
                    }
                }
                ExcelRange cells = sheet.Cells[1, 1, 1 + dt.Rows.Count, keyList.Count];
                cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
                cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
                cells.AutoFitColumns();//自适应列宽
                result = package.GetAsByteArray();
            }
            return result;
        }
    }
}
